/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Enquiry;

import Category.CategoryBean;
import Course.CourseBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(EnquiryBean eb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into enquiry values(enquiryid.nextval,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?)");
            pre.setString(1, eb.getStudentName());
            pre.setDate(2, eb.getDob());
            pre.setDate(3, eb.getDor());
            pre.setString(4, eb.getGender());
            pre.setString(5, eb.getFatherName());
            pre.setString(6, eb.getMobileNo());
            pre.setString(7, eb.getEmail());
            pre.setInt(8, eb.getCourseId());
            pre.setString(9, eb.getStream());
            pre.setInt(10, eb.getProspectus());
            pre.setString(11, eb.getEntryEmail());
            pre.setString(12, eb.getEntranceExName());
            pre.setString(13, eb.getMarks());
            pre.setString(14, eb.getRank());
            pre.setString(15, eb.getoEntranceExName());
            pre.setString(16, eb.getoMarks());
            pre.setString(17, eb.getoRank());
            pre.setInt(18, eb.getCategoryId());
            i = pre.executeUpdate();
            if (i != 0) {
                k=getMaxId();
            }
        } catch (Exception e) {
            return k;
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(enquiry_id) from enquiry");
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {

                i=rs.getInt(1);
            }
        }
            catch(Exception e)
            {
                System.out.println(e);
            }
        return i;
    }

    public static Vector viewAll() throws SQLException
    {
       Vector<Object> list=new Vector<Object>();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from enquiry e left join course c on e.course_id=c.course_id left join category ca on e.category_id=ca.category_id");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               EnquiryBean eb=new EnquiryBean();
               CourseBean cb=new CourseBean();
               CategoryBean cab=new CategoryBean();
               eb.setEnquiryId(rs.getInt(1));
               eb.setStudentName(rs.getString(2));
               eb.setDob(rs.getDate(3));
               eb.setDor(rs.getDate(4));
               eb.setGender(rs.getString(5));
               eb.setFatherName(rs.getString(6));
               eb.setMobileNo(rs.getString(7));
               eb.setEmail(rs.getString(8));
               eb.setCourseId(rs.getInt(9));
               eb.setStream(rs.getString(10));
               eb.setProspectus(rs.getInt(11));
               eb.setEntryDate(rs.getDate(12));
               eb.setEntryEmail(rs.getString(13));
               eb.setEntranceExName(rs.getString(14));
               eb.setMarks(rs.getString(15));
               eb.setRank(rs.getString(16));
               eb.setoEntranceExName(rs.getString(17));
               eb.setoMarks(rs.getString(18));
               eb.setoRank(rs.getString(19));
               eb.setCategoryId(rs.getInt(20));
               cb.setCourseId(rs.getInt(21));
               cb.setCourseName(rs.getString(22));
               cb.setCourseType(rs.getInt(23));
               cb.setCoursePeriod(rs.getInt(24));
               cb.setStream(rs.getString(25));
               cb.setEntryDate(rs.getDate(26));
               cab.setCategoryId(rs.getInt(27));
               cab.setCategory(rs.getString(28));
               cab.setEntryDate(rs.getDate(29));
               eb.setCourseBean(cb);
               eb.setCategoryBean(cab);
               list.add(eb);
           }
       }
       catch(Exception e){
       System.out.println();
       }finally{con.close();}
       return list;
    }
    
    public static EnquiryBean viewSpecific(String cname, String val) throws SQLException
    {
        EnquiryBean eb=new EnquiryBean();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from enquiry e left join course c on e.course_id=c.course_id left join category ca on e.category_id=ca.category_id where "+cname+"="+val+"");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               CourseBean cb=new CourseBean();
               CategoryBean cab=new CategoryBean();
               eb.setEnquiryId(rs.getInt(1));
               eb.setStudentName(rs.getString(2));
               eb.setDob(rs.getDate(3));
               eb.setDor(rs.getDate(4));
               eb.setGender(rs.getString(5));
               eb.setFatherName(rs.getString(6));
               eb.setMobileNo(rs.getString(7));
               eb.setEmail(rs.getString(8));
               eb.setCourseId(rs.getInt(9));
               eb.setStream(rs.getString(10));
               eb.setProspectus(rs.getInt(11));
               eb.setEntryDate(rs.getDate(12));
               eb.setEntryEmail(rs.getString(13));
               eb.setEntranceExName(rs.getString(14));
               eb.setMarks(rs.getString(15));
               eb.setRank(rs.getString(16));
               eb.setoEntranceExName(rs.getString(17));
               eb.setoMarks(rs.getString(18));
               eb.setoRank(rs.getString(19));
               eb.setCategoryId(rs.getInt(20));
               cb.setCourseId(rs.getInt(21));
               cb.setCourseName(rs.getString(22));
               cb.setCourseType(rs.getInt(23));
               cb.setCoursePeriod(rs.getInt(24));
               cb.setStream(rs.getString(25));
               cb.setEntryDate(rs.getDate(26));
               cab.setCategoryId(rs.getInt(27));
               cab.setCategory(rs.getString(28));
               cab.setEntryDate(rs.getDate(29));
               eb.setCourseBean(cb);
               eb.setCategoryBean(cab);
           }
       }
       catch(Exception e){
       System.out.println();
       }finally{con.close();}
       return eb;
    }
    
    public static int delete(int id) throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("delete from enquiry where enquiry_id=?");
            pre.setInt(1, id);
            i=pre.executeUpdate();
        }
        catch(Exception e)
        {
            return i;
        }finally{
            con.close();
        }
        return i;
    }
    public static int edit(EnquiryBean eb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update enquiry set STUDENT_NAME=?,DOB=?,DOR=?,GENDER=?,FATHER_NAME=?,MOBILE_NO=?,EMAIL=?,COURSE_ID=?,STREAM=?,PROSPECTUS=?,ENTRY_DATE=sysdate,ENTRY_EMAIL=?,ENTRANCE_EXAM_NAME=?,EX_MARK=?,EX_RANK=?,O_ENTRANCE_EXAM_NAME=?,O_EX_MARK=?,O_EX_RANK=?,CATEGORY_ID=? where enquiry_id=?");
            pre.setString(1, eb.getStudentName());
            pre.setDate(2, eb.getDob());
            pre.setDate(3, eb.getDor());
            pre.setString(4, eb.getGender());
            pre.setString(5, eb.getFatherName());
            pre.setString(6, eb.getMobileNo());
            pre.setString(7, eb.getEmail());
            pre.setInt(8, eb.getCourseId());
            pre.setString(9, eb.getStream());
            pre.setInt(10, eb.getProspectus());
            pre.setString(11, eb.getEntryEmail());
            pre.setString(12, eb.getEntranceExName());
            pre.setString(13, eb.getMarks());
            pre.setString(14, eb.getRank());
            pre.setString(15, eb.getoEntranceExName());
            pre.setString(16, eb.getoMarks());
            pre.setString(17, eb.getoRank());
            pre.setInt(18, eb.getCategoryId());
            pre.setInt(19, eb.getEnquiryId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    } 
    public static Vector SearchByName(String name) throws SQLException
    {
       Vector<Object> list=new Vector<Object>();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from enquiry e left join course c on e.course_id=c.course_id left join category ca on e.category_id=ca.category_id where upper(e.STUDENT_NAME) like upper('%"+name+"%') or lower(e.FATHER_NAME) like lower('%"+name+"%')");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               EnquiryBean eb=new EnquiryBean();
               CourseBean cb=new CourseBean();
               CategoryBean cab=new CategoryBean();
               eb.setEnquiryId(rs.getInt(1));
               eb.setStudentName(rs.getString(2));
               eb.setDob(rs.getDate(3));
               eb.setDor(rs.getDate(4));
               eb.setGender(rs.getString(5));
               eb.setFatherName(rs.getString(6));
               eb.setMobileNo(rs.getString(7));
               eb.setEmail(rs.getString(8));
               eb.setCourseId(rs.getInt(9));
               eb.setStream(rs.getString(10));
               eb.setProspectus(rs.getInt(11));
               eb.setEntryDate(rs.getDate(12));
               eb.setEntryEmail(rs.getString(13));
               eb.setEntranceExName(rs.getString(14));
               eb.setMarks(rs.getString(15));
               eb.setRank(rs.getString(16));
               eb.setoEntranceExName(rs.getString(17));
               eb.setoMarks(rs.getString(18));
               eb.setoRank(rs.getString(19));
               eb.setCategoryId(rs.getInt(20));
               cb.setCourseId(rs.getInt(21));
               cb.setCourseName(rs.getString(22));
               cb.setCourseType(rs.getInt(23));
               cb.setCoursePeriod(rs.getInt(24));
               cb.setStream(rs.getString(25));
               cb.setEntryDate(rs.getDate(26));
               cab.setCategoryId(rs.getInt(27));
               cab.setCategory(rs.getString(28));
               cab.setEntryDate(rs.getDate(29));
               eb.setCourseBean(cb);
               eb.setCategoryBean(cab);
               list.add(eb);
           }
       }
       catch(Exception e){
       System.out.println();
       }finally{con.close();}
       return list;
    }
    public static Vector CourseWiseR(int name) throws SQLException
    {
       Vector<Object> list=new Vector<Object>();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from enquiry e left join course c on e.course_id=c.course_id left join category ca on e.category_id=ca.category_id where c.COURSE_ID  ="+name+"");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               EnquiryBean eb=new EnquiryBean();
               CourseBean cb=new CourseBean();
               CategoryBean cab=new CategoryBean();
               eb.setEnquiryId(rs.getInt(1));
               eb.setStudentName(rs.getString(2));
               eb.setDob(rs.getDate(3));
               eb.setDor(rs.getDate(4));
               eb.setGender(rs.getString(5));
               eb.setFatherName(rs.getString(6));
               eb.setMobileNo(rs.getString(7));
               eb.setEmail(rs.getString(8));
               eb.setCourseId(rs.getInt(9));
               eb.setStream(rs.getString(10));
               eb.setProspectus(rs.getInt(11));
               eb.setEntryDate(rs.getDate(12));
               eb.setEntryEmail(rs.getString(13));
               eb.setEntranceExName(rs.getString(14));
               eb.setMarks(rs.getString(15));
               eb.setRank(rs.getString(16));
               eb.setoEntranceExName(rs.getString(17));
               eb.setoMarks(rs.getString(18));
               eb.setoRank(rs.getString(19));
               eb.setCategoryId(rs.getInt(20));
               cb.setCourseId(rs.getInt(21));
               cb.setCourseName(rs.getString(22));
               cb.setCourseType(rs.getInt(23));
               cb.setCoursePeriod(rs.getInt(24));
               cb.setStream(rs.getString(25));
               cb.setEntryDate(rs.getDate(26));
               cab.setCategoryId(rs.getInt(27));
               cab.setCategory(rs.getString(28));
               cab.setEntryDate(rs.getDate(29));
               eb.setCourseBean(cb);
               eb.setCategoryBean(cab);
               list.add(eb);
           }
       }
       catch(Exception e){
       System.out.println();
       }finally{con.close();}
       return list;
    }
    public static Vector viewAlldistinctExam() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select distinct (ENTRANCE_EXAM_NAME) from enquiry");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                EnquiryBean eb=new EnquiryBean();
                eb.setEntranceExName(rs.getString(1));
                list.add(eb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
     public static Vector ExameWiseR(String name) throws SQLException
    {
       Vector<Object> list=new Vector<Object>();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from enquiry e left join course c on e.course_id=c.course_id left join category ca on e.category_id=ca.category_id where e.ENTRANCE_EXAM_NAME='"+name+"'");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               EnquiryBean eb=new EnquiryBean();
               CourseBean cb=new CourseBean();
               CategoryBean cab=new CategoryBean();
               eb.setEnquiryId(rs.getInt(1));
               eb.setStudentName(rs.getString(2));
               eb.setDob(rs.getDate(3));
               eb.setDor(rs.getDate(4));
               eb.setGender(rs.getString(5));
               eb.setFatherName(rs.getString(6));
               eb.setMobileNo(rs.getString(7));
               eb.setEmail(rs.getString(8));
               eb.setCourseId(rs.getInt(9));
               eb.setStream(rs.getString(10));
               eb.setProspectus(rs.getInt(11));
               eb.setEntryDate(rs.getDate(12));
               eb.setEntryEmail(rs.getString(13));
               eb.setEntranceExName(rs.getString(14));
               eb.setMarks(rs.getString(15));
               eb.setRank(rs.getString(16));
               eb.setoEntranceExName(rs.getString(17));
               eb.setoMarks(rs.getString(18));
               eb.setoRank(rs.getString(19));
               eb.setCategoryId(rs.getInt(20));
               cb.setCourseId(rs.getInt(21));
               cb.setCourseName(rs.getString(22));
               cb.setCourseType(rs.getInt(23));
               cb.setCoursePeriod(rs.getInt(24));
               cb.setStream(rs.getString(25));
               cb.setEntryDate(rs.getDate(26));
               cab.setCategoryId(rs.getInt(27));
               cab.setCategory(rs.getString(28));
               cab.setEntryDate(rs.getDate(29));
               eb.setCourseBean(cb);
               eb.setCategoryBean(cab);
               list.add(eb);
           }
       }
       catch(Exception e){
       System.out.println();
       }finally{con.close();}
       return list;
    }
}
